﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using Itau.SC.EnterpriseLib.Data;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using itau.im.negocio;
using itau.im.entidade;

namespace Curva_Interpolada
{
    class Banco
    {
        #region "Propriedades"

        Database _dbSQL;
        public Database DbSQL
        {
            set { _dbSQL = value; }
            get { return _dbSQL; }
        }

        Database _dbDB2;
        public Database DbDB2
        {
            set { _dbDB2 = value; }
            get { return _dbDB2; }
        }

        string gbPrefixo;
        public string PgbPrefixo
        {
            set { gbPrefixo = value; }
            get { return gbPrefixo; }
        }

        string gbDataSource;
        public string PgbDataSource
        {
            set { gbDataSource = value; }
            get { return gbDataSource; }
        }

        public string strAmbiente;
        public string pStrAmbiente
        {
            set { strAmbiente = value; }
            get { return strAmbiente; }
        }

        public DbConnection _connection;
        public DbConnection Connection
        {
            set { _connection = value; }
            get { return _connection; }
        }

        public DbTransaction _transaction;
        public DbTransaction Transaction
        {
            set { _transaction = value; }
            get { return _transaction; }
        }

        #endregion "Propriedades"

        #region "Métodos"

        public bool ConectaSQL(string pAmbiente)
        {
            if (pAmbiente == "P")
            {
                try
                {
                    DbSQL = DatabaseFactory.CreateDatabase("ConectaSQLProd");
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
            else
            {
                try
                {
                    DbSQL = DatabaseFactory.CreateDatabase("ConectaSQLHom");
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }

        public bool ConectaDb2(string pAmbiente)
        {
            //Iniciando tentativa de conexão com o DB2.
            try
            {
                if (pAmbiente == "H")
                {
                    PgbPrefixo = "DB2CAS.";
                    PgbDataSource = "DB2DES";
                    _dbDB2 = DatabaseFactory.CreateDatabase("ConectaDB2Hom");
                    return true;
                }

                if (pAmbiente == "P")
                {
                    PgbPrefixo = "DB2GPP.";
                    PgbDataSource = "DB2PRO";
                    _dbDB2 = DatabaseFactory.CreateDatabase("ConectaDB2Prod");
                    return true;
                }
                else
                    return false;

            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public void BeginTransaction()
        {
            //_db = DatabaseFactory.CreateDatabase(System.Configuration.ConfigurationSettings.AppSettings["DB2_CONNECTION"]);
            Connection = _dbDB2.CreateConnection();
            Connection.Open();
            Transaction = Connection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            _transaction.Commit();
        }

        public void RollBackTransaction()
        {
            _transaction.Rollback();
        }


        public int ConsultaCurvas(Int32 iCurvaId)
        {
            if (_dbDB2 == null)
            {
                ConectaDb2(ConfigurationSettings.AppSettings["Ambiente"]);
            }
            DbCommand command = _dbDB2.GetStoredProcCommand("DB2IM.IMSFI");
            try
            {
                DbDB2.AddInParameter(command, "@COD_CURV", DbType.Int32, iCurvaId);
                DbDB2.AddOutParameter(command, "@NOM_INRN_CURV", DbType.String, 15);
                DbDB2.AddOutParameter(command, "@NOM_CURV", DbType.String, 80);
                DbDB2.AddOutParameter(command, "@DES_CURV", DbType.String, 120);
                DbDB2.AddOutParameter(command, "@COD_MODA_CURV", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_CATE_MERC", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_SITU", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_TIPO_CURV", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_SUB_MESA_OPER", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@CCOD_MESA_OPER_TES", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@QTD_ANO_EXTE", DbType.Int16, 2);//
                DbDB2.AddOutParameter(command, "@DAT_ULTI_ALTE", DbType.DateTime, 26);
                DbDB2.AddOutParameter(command, "@IND_LIBE_AUTA", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@IND_PMTE_VERS", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@IND_TRCI_CURV", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_TIPO_CURV_PAD", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_CLAF_CURV", DbType.Int16, 2);

                DbDB2.ExecuteDataSet(command);
            }
            catch (Exception ex)
            {
            }
            
            return Convert.ToInt32(DbDB2.GetParameterValue(command, "@QTD_ANO_EXTE"));
        }

        public DateTime ConsultaVigenciaCurva(Int32 iCurvaId, DateTime dataHoraExecucao)
        {
            if (_dbDB2 == null)
            {
                ConectaDb2(ConfigurationSettings.AppSettings["Ambiente"]);
            }
            DbCommand command = _dbDB2.GetStoredProcCommand("DB2IM.IMS2D");
            try
            {
                //Adiciona Parametro de entrada
                DbDB2.AddInParameter(command, "@DAT_CURV_VIGE", DbType.DateTime, dataHoraExecucao);
                DbDB2.AddInParameter(command, "@COD_CURV", DbType.Int32, iCurvaId);
                DbDB2.AddOutParameter(command, "@DAT_INCU_VIGE", DbType.DateTime, 26);
                DbDB2.AddOutParameter(command, "@COD_MODE_CALC", DbType.Int16, 4);
                DbDB2.AddOutParameter(command, "@COD_TIPO_ENTR_DADO", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_TIPO_PRZ", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_TIPO_PRZ_ENTR", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_MOED", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_TIPO_TXA", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_MODE_INTV_TOLE", DbType.Int32, 4);
                DbDB2.AddOutParameter(command, "@DAT_INIO_CURV_VIGE", DbType.DateTime, 26);
                DbDB2.AddOutParameter(command, "@DAT_FIM_CURV_VIGE", DbType.DateTime, 26);
                DbDB2.AddOutParameter(command, "@IND_PMRO_FATR_1", DbType.Int16, 2);
                DbDB2.AddOutParameter(command, "@COD_CURV_BASE_PMT", DbType.Int32, 4);
                DbDB2.AddOutParameter(command, "@IND_PMRO_LIBR_DFL", DbType.Int16, 2);

                //Recupera a consulta
                DbDB2.ExecuteDataSet(command);
            }
            catch (Exception ex)
            {

            }

            return Convert.ToDateTime(DbDB2.GetParameterValue(command, "@DAT_INCU_VIGE"));

        }

        public bool InsereRegistroCurva(Int32 iCodCurva, DateTime dtDataIncVigencia, DateTime dtDataHoraExecCurva, Int32 iCodSituacao, Int32 iCodTipoEventoNotificacao, DateTime dtDataHoraLiberacaoCurva)
        {
            //if (_dbDB2 == null)
            //{
            //    ConectaDb2(ConfigurationSettings.AppSettings["Ambiente"]);
            //}
            

            DbCommand command = _dbDB2.GetStoredProcCommand("DB2IM.IMSKI");
            
            try
            {
                DbDB2.AddInParameter(command,"@COD_CURV",DbType.Int32, iCodCurva); //CodCurvaNovo
                DbDB2.AddInParameter(command, "@DAT_INCU_VIGE", DbType.DateTime, dtDataIncVigencia); // BaseNova
                DbDB2.AddInParameter(command, "@DAT_HOR_EXEO_CURV", DbType.DateTime, dtDataHoraExecCurva); //dtDataRef + horário(09:00:00)
                DbDB2.AddInParameter(command, "@COD_SITU", DbType.Int32, iCodSituacao); // 40

                if (iCodTipoEventoNotificacao != 0)
                {
                    DbDB2.AddInParameter(command, "@COD_TIPO_EVEN_NOT", DbType.Int32, iCodTipoEventoNotificacao); //0
                }
                else
                {
                    DbDB2.AddInParameter(command, "@COD_TIPO_EVEN_NOT", DbType.Int32, DBNull.Value);
                }

                if (dtDataHoraLiberacaoCurva != DateTime.MinValue)
                {
                    DbDB2.AddInParameter(command, "@DAT_HOR_LIBE_CURV", DbType.DateTime, dtDataHoraLiberacaoCurva); //dtDataRef + horário
                }
                else
                {
                    DbDB2.AddInParameter(command, "@DAT_HOR_LIBE_CURV", DbType.DateTime, DBNull.Value);
                }

                //Executar utilizando transação.
                DbDB2.ExecuteNonQuery(command, Transaction);

                return true;

            }
            catch (Exception ex)
            {
                return false;
            }
            
        }

        public bool InserePontosCurva(Int32 iCodCurva, DateTime dtDataIncVigencia, DateTime dtDataHoraExecCurva, DateTime dtDataVenciamento, double dValorVertice, Int32 iQtdeDiasCorridos, Int32 iQtdeDiasUteis, Int32 iIndicadorVertice)
        {
            //if (_dbDB2 == null)
            //{
            //    ConectaDb2(ConfigurationSettings.AppSettings["Ambiente"]);
            //}

            DbCommand command = _dbDB2.GetStoredProcCommand("DB2IM.IMSKJ");

            try
            {
                DbDB2.AddInParameter(command, "@COD_CURV", DbType.Int32, iCodCurva);
                DbDB2.AddInParameter(command, "@DAT_INCU_VIGE", DbType.DateTime, dtDataIncVigencia);
                DbDB2.AddInParameter(command, "@DAT_HOR_EXEO_CURV", DbType.DateTime, dtDataHoraExecCurva);
                DbDB2.AddInParameter(command, "@DAT_VCTO", DbType.Date, dtDataVenciamento);

                if (double.IsInfinity(dValorVertice) || double.IsNaN(dValorVertice))
                {
                    DbDB2.AddInParameter(command, "@VLR_PONT", DbType.Double, -1);
                }
                else
                {
                    DbDB2.AddInParameter(command, "@VLR_PONT", DbType.Double, dValorVertice);
                }

                DbDB2.AddInParameter(command, "@QTD_DIA_CRRI", DbType.Int32, iQtdeDiasCorridos);
                DbDB2.AddInParameter(command, "@QTD_DIA_UTIL", DbType.Int32, iQtdeDiasUteis);
                DbDB2.AddInParameter(command, "@IND_VRTE", DbType.Int16, iIndicadorVertice);

                DbDB2.ExecuteNonQuery(command, Transaction);
                return true;

            }
            catch (Exception ex)
            {
                return false;
            }

        }

        public bool InsereCurvaBD2(Int32 iCodCurva, DateTime dtDataIncVigencia, DateTime dtDataHoraExecCurva, 
            Int32 iCodSituacao, Int32 iCodTipoEventoNotificacao, DateTime dtDataHoraLiberacaoCurva, List<PontoCurva> lista)
        {
            try
            {
                if (_dbDB2 == null)
                {
                    ConectaDb2(ConfigurationSettings.AppSettings["Ambiente"]);
                }

                BeginTransaction();

                InsereRegistroCurva(iCodCurva, dtDataIncVigencia, dtDataHoraExecCurva, iCodSituacao, iCodTipoEventoNotificacao, dtDataHoraLiberacaoCurva);

                IList<CurvaExecucaoPonto> listaCurvaExecucaoPonto = new List<CurvaExecucaoPonto>();
                foreach (PontoCurva itemPonto in lista)
                {
                    listaCurvaExecucaoPonto.Add(new CurvaExecucaoPonto()
                    {
                        CodigoCurva = itemPonto.iCodCurva,
                        DataHoraExecucaoCurva = itemPonto.dtDtHoraExecLib,
                        DataInclusaoVigencia = itemPonto.dtVigencia,
                        DataVencimento = itemPonto.dtVencimento,
                        IndicadorVertice = itemPonto.iIndicadorDeVertice,
                        QuantidadeDiasCorridos = itemPonto.iDiasCorridos,
                        QuantidadeDiasUteis = itemPonto.iDiasUteis,
                        ValorVertice = itemPonto.dValorVertice
                    });
                }

                IExecucaoCurvaBS execucaoCurvaBS = new ExecucaoCurvaBS();
                execucaoCurvaBS.CriarCurvaExecucaoPontos(listaCurvaExecucaoPonto);
                
                CommitTransaction();

                return true;
            }
            catch (Exception ex)
            {
                RollBackTransaction();
                return false;
            }
            finally
            {
                Connection.Close();
            }
        }


    #endregion ""

    }
}
